package main.role;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;

import main.staff.CreateTypesSql;
import main.util.SqlConstants;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class CreateSql {

	public static FileWriter staffInfo,role,serviceType,password;
	
	public static void main(String[] args) {
		InputStream inp;
		try {
			inp = new FileInputStream("e:/按钮角色信息.xlsx");
			//InputStream inp = new FileInputStream("workbook.xlsx");
			
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(1);

			 staffInfo = new FileWriter("D:/人员信息t_ucp_staffbasicinfo.sql");
			 role= new FileWriter("D:/角色t_ucp_staffrole.sql");
			 serviceType=new FileWriter("D:/业务类型t_ucp_staffserviceType.sql");
			 password=new FileWriter("D:/密码t_ucp_password.sql");
			 
			 SqlConstants.topWriteHead(role);
			 operateAuthRole(sheet);
			 
			 SqlConstants.topWriteEnd(staffInfo);
			 SqlConstants.topWriteEnd(role);
			 SqlConstants.topWriteEnd(serviceType);
			 SqlConstants.topWriteEnd(password);
			 
			staffInfo.close();
			role.close();
			serviceType.close();
			password.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
		}
	}
	
	
	public static void operateAuthRole(Sheet sheet){
		try {
			for(int i=0;i<sheet.getLastRowNum()+1;i++){
				Row row = sheet.getRow(i);
				String authId=SqlConstants.getColValue(row, 5);
				
				if(authId.matches("[0-9]{14,}+")){
					//staffInfo.write(CreateTypesSql.staffbasicinfo(row,staffid));
					role.write(RoleAuthelement.getRoleSql(row, authId, 6, 16));
					//serviceType.write(CreateTypesSql.staffserviceType(row,staffid));
					//password.write(CreateTypesSql.password(staffid));
					System.out.println(authId+":成功   "+i);
				}else{
					System.out.println(authId+":loss  "+i);
				}
			}
		} catch (IOException e) {
			System.out.println("'''''''''''''''''''''''''"+sheet.getSheetName()+"添加失败");
			e.printStackTrace();
		}
	}
	
	public static void operateSheet(Sheet sheet){
		try {
			for(int i=0;i<sheet.getLastRowNum()+1;i++){
				Row row = sheet.getRow(i);
				String staffid=CreateTypesSql.removeNull(row, 5);
				
				if(staffid.matches("[a-zA-Z]{2}?[0-9]{5}?")){
					//staffInfo.write(CreateTypesSql.staffbasicinfo(row,staffid));
					role.write(Role.getRoleSql(row, staffid, 10, 20));
					//serviceType.write(CreateTypesSql.staffserviceType(row,staffid));
					//password.write(CreateTypesSql.password(staffid));
					System.out.println(staffid+":成功   "+i);
				}else{
					System.out.println(staffid+":loss  "+i);
				}
			}
		} catch (IOException e) {
			System.out.println("'''''''''''''''''''''''''"+sheet.getSheetName()+"添加失败");
			e.printStackTrace();
		}
	}
	
	public static void main2(String[] args){
		System.out.println(CreateTypesSql.password("0682"));
	}
	
}
